﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using Skyworth.CRM.Service.Entity;
using Skyworth.CRM.Service.IDAL;

namespace Skyworth.CRM.DAL
{
   public class OrdersDAL:SuperDataBase,IOrdersDAL
    {
       public string AddOrder(Order model, DataTable orderDetail,Boolean isSubmit)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append(" declare @id int;if not exists(select 1 from T_Order where Fnumber=@orderCode) begin");
           strSql.Append(" begin transaction  "); //set @orderCode=dbo.fn_GetNewOrderNo();declare @orderCode nvarchar(15);
           strSql.Append(" insert into T_Order(Ftitle,Fnumber,FCustomersID,FCusDreeID,FcontactID,FstockID,FnoteID,Fupdater,FUpdateDate,FChecker,FCheckDate,Fstatus,Fqty,Fprice,Fvolume,FtaxPrice)");
           strSql.Append(" values(@Ftitle,@orderCode,@FCustomersID,@FCusDreeID,@FContactID,@FstockID,@FnoteID,@Fupdater,getdate(),null,getdate(),0,@Fqty,@Fprice,@Fvolume,@FtaxPrice)");

           strSql.Append(" set @id=scope_identity();");
           if(orderDetail!=null && orderDetail.Rows.Count>0)
           {
               for (int i = 0; i < orderDetail.Rows.Count;i++ )
               {
                   string isFanLi = orderDetail.Rows[i]["PriceType"].ToString();
                   isFanLi = isFanLi.ToLower() == "false" || isFanLi == "0" ? "0" : "1";

                   strSql.Append(" insert into T_OrderEntry( FOrderID,FEntryID,FProductid,Fqty,FUnitVolume,Fqtyvolume,Funit,Ftax,Fprice,Ftaxprice,Famount,Ftaxamount,Ffanli,Fnote,FProductName)");
                   strSql.Append(" values(@id," + (i+1).ToString() + "," + orderDetail.Rows[i]["ProductId"] + "," + orderDetail.Rows[i]["OrderNumber"] + ",'" + orderDetail.Rows[i]["UnitVolume"]
                       + "','" + orderDetail.Rows[i]["TotalVolume"] + "','" + orderDetail.Rows[i]["Unit"] + "'," + orderDetail.Rows[i]["TaxRatio"] + ","+orderDetail.Rows[i]["Price"]+
                       "," + orderDetail.Rows[i]["TaxPrice"] + "," + orderDetail.Rows[i]["TotalPrie"] + "," + orderDetail.Rows[i]["TotalTaxPrice"] +","+
                       isFanLi + ",'" + orderDetail.Rows[i]["Notes"] + "','" + orderDetail.Rows[i]["ProdctName"] + "')");
               }
           }
           if(isSubmit)
           {
               strSql.Append(" exec dbo.sp_SubmitOrder @id, 1");
           }
           strSql.Append(" IF (@@ERROR>0) ROLLBACK ELSE COMMIT;select @id; ");
           strSql.Append(" end else  begin Raiserror('订单编号重复',16,1) end ");

           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@Ftitle", DbType.String, model.OrderName);
           DB.AddInParameter(cmd, "@FCustomersID", DbType.Int32, model.CustId);
           DB.AddInParameter(cmd, "@FCusDreeID", DbType.Int32, model.CusDreeId);
           DB.AddInParameter(cmd, "@FContactID", DbType.Int32, model.ContactId);
           DB.AddInParameter(cmd, "@FstockID", DbType.Int32, model.StockId);
           DB.AddInParameter(cmd, "@FnoteID", DbType.String, model.Notes);
           DB.AddInParameter(cmd, "@Fupdater", DbType.Int32, model.Creator);
           DB.AddInParameter(cmd, "@orderCode", DbType.String, model.OrderCode);
           //DB.AddInParameter(cmd, "@FUpdateDate", DbType.DateTime, model.CreateTime);
           //DB.AddInParameter(cmd, "@FChecker", DbType.Int32, model.SubmiterId);
           //DB.AddInParameter(cmd, "@FCheckDate", DbType.DateTime, model.SubmitTime);
           DB.AddInParameter(cmd, "@Fqty", DbType.Double, model.TotalNumber);
           DB.AddInParameter(cmd, "@Fprice", DbType.Decimal, model.TotalPrice);
           DB.AddInParameter(cmd, "@Fvolume", DbType.Double, model.TotalVolume);
           DB.AddInParameter(cmd, "@FtaxPrice", DbType.Decimal, model.TotalTaxPrice);
           object orderNo=DB.ExecuteScalar(cmd);
           return Convert.ToString(orderNo);
       }

       public Order GetOrderById(int orderId, out DataTable orderDetail)
       {
           Order model = null;
           orderDetail = new DataTable();
           StringBuilder strSql = new StringBuilder();
           strSql.Append(
               "select  top 1 T_Order.Fitemid,Ftitle,T_Order.Fnumber,FCustomersID,FCusDreeID,FcontactID,FstockID,FnoteID,Fupdater,");
           strSql.Append(" FUpdateDate,Fchecker,FCheckDate,Fstatus,Fqty,Fprice,Fvolume,FtaxPrice,T_BasicData_Customer.FName as CustName from T_Order ");
           strSql.Append(" left join T_BasicData_Customer on T_Order.FCustomersID=T_BasicData_Customer.Fitemid ");
           strSql.Append(" where T_Order.Fitemid=@orderId");
           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@orderId", DbType.Int32, orderId);
           using (IDataReader dr = DB.ExecuteReader(cmd))
           {
               while (dr.Read())
               {
                   model = new Order();
                   model.Id = orderId;
                   model.OrderCode = dr["Fnumber"].ToString();
                   model.OrderName = dr["Ftitle"].ToString();
                   model.CustId = Convert.ToInt32(dr["FCustomersID"].ToString());
                   model.CusDreeId = Convert.ToInt32(dr["FCusDreeID"].ToString());
                   model.ContactId = Convert.ToInt32(dr["FcontactID"].ToString());
                   model.StockId = Convert.ToInt32(dr["FstockID"].ToString());
                   model.Notes = dr["FnoteID"].ToString();
                   model.Creator = Convert.ToInt32(dr["Fupdater"].ToString());
                   model.CreateTime = Convert.ToDateTime(dr["FUpdateDate"].ToString());
                   int submiterId = 0;
                   int.TryParse(dr["Fchecker"].ToString(), out submiterId);
                   model.SubmiterId = submiterId;
                   model.SubmitTime = Convert.ToDateTime(dr["FCheckDate"].ToString());
                   model.Status = Convert.ToInt16(dr["Fstatus"].ToString());
                   model.TotalNumber = Convert.ToDouble(dr["Fqty"].ToString());
                   model.TotalPrice = Convert.ToDecimal(dr["Fprice"].ToString());
                   model.TotalTaxPrice = Convert.ToDecimal(dr["FtaxPrice"].ToString());
                   model.TotalVolume = Convert.ToDouble(dr["Fvolume"].ToString());
                   model.CustomerName = dr["CustName"].ToString();
               }
           }
           orderDetail = GetOrderDetail(orderId);
           return model;
       }

       public bool UpdateOrder(Order model, DataTable orderDetail, Boolean isSubmit)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append(" declare @status int=0; if not exists(select 1 from T_Order where Fnumber=@orderCode and Fitemid<>@orderId) begin begin transaction select @status=Fstatus from T_Order where Fitemid=@orderId ");
           strSql.Append(
               " update T_Order set Ftitle=@Ftitle,FCustomersID=@FCustomersID,FCusDreeID=@FCusDreeID,FcontactID=@FContactID,FstockID=@FstockID,FnoteID=@FnoteID,FChecker=@Fupdater,Fnumber=@orderCode,");
           strSql.Append(" FCheckDate=getdate(),Fqty=@Fqty,Fprice=@Fprice,Fvolume=@Fvolume,FtaxPrice=@FtaxPrice where Fitemid=@orderId ");
           strSql.Append(" delete T_OrderEntry where FOrderid=@orderId ");

           if (orderDetail != null && orderDetail.Rows.Count > 0)
           {
               for (int i = 0; i < orderDetail.Rows.Count; i++)
               {
                   string isFanLi = orderDetail.Rows[i]["PriceType"].ToString();
                   isFanLi = isFanLi.ToLower() == "false" || isFanLi == "0" ? "0" : "1";

                   strSql.Append(" insert into T_OrderEntry( FOrderID,FEntryID,FProductid,Fqty,FUnitVolume,Fqtyvolume,Funit,Ftax,Fprice,Ftaxprice,Famount,Ftaxamount,Ffanli,Fnote,FProductName)");
                   strSql.Append(" values(@orderId," + (i + 1).ToString() + "," + orderDetail.Rows[i]["ProductId"] + "," + orderDetail.Rows[i]["OrderNumber"] + ",'" + orderDetail.Rows[i]["UnitVolume"]
                       + "','" + orderDetail.Rows[i]["TotalVolume"] + "','" + orderDetail.Rows[i]["Unit"] + "'," + orderDetail.Rows[i]["TaxRatio"] + "," + orderDetail.Rows[i]["Price"] +
                       "," + orderDetail.Rows[i]["TaxPrice"] + "," + orderDetail.Rows[i]["TotalPrie"] + "," + orderDetail.Rows[i]["TotalTaxPrice"] + "," +
                       isFanLi + ",'" + orderDetail.Rows[i]["Notes"] + "','" + orderDetail.Rows[i]["ProdctName"] + "')");
               }
           }
           if (isSubmit)
           {
               strSql.Append(" if(@status=5) exec dbo.sp_ReSubmit @orderId,1 else exec dbo.sp_SubmitOrder @orderId, 1");
           }

           strSql.Append(" IF (@@ERROR>0) ROLLBACK ELSE COMMIT;");
           strSql.Append(" end else  begin Raiserror('订单编号重复',16,1) end ");

           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@Ftitle", DbType.String, model.OrderName);
           DB.AddInParameter(cmd, "@FCustomersID", DbType.Int32, model.CustId);
           DB.AddInParameter(cmd, "@FCusDreeID", DbType.Int32, model.CusDreeId);
           DB.AddInParameter(cmd, "@FContactID", DbType.Int32, model.ContactId);
           DB.AddInParameter(cmd, "@FstockID", DbType.Int32, model.StockId);
           DB.AddInParameter(cmd, "@FnoteID", DbType.String, model.Notes);
           DB.AddInParameter(cmd, "@Fupdater", DbType.Int32, model.Creator);
           DB.AddInParameter(cmd, "@orderCode", DbType.String, model.OrderCode);
           //DB.AddInParameter(cmd, "@FUpdateDate", DbType.DateTime, model.CreateTime);
           //DB.AddInParameter(cmd, "@FChecker", DbType.Int32, model.SubmiterId);
           //DB.AddInParameter(cmd, "@FCheckDate", DbType.DateTime, model.SubmitTime);
           DB.AddInParameter(cmd, "@Fqty", DbType.Double, model.TotalNumber);
           DB.AddInParameter(cmd, "@Fprice", DbType.Decimal, model.TotalPrice);
           DB.AddInParameter(cmd, "@Fvolume", DbType.Double, model.TotalVolume);
           DB.AddInParameter(cmd, "@FtaxPrice", DbType.Decimal, model.TotalTaxPrice);
           DB.AddInParameter(cmd, "@orderId", DbType.Int32, model.Id);
           DB.ExecuteNonQuery(cmd);
           return true;
       }

       public DataTable GetOrdersByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
       {
           recordCount = 0;
           pageCount = 0;
           StringBuilder strCommandText = new StringBuilder();
           DataSet ds;
           strCommandText.AppendFormat(
                  "SELECT row_number() over({0}) AS rownum, T_Order.FitemId, T_Order.Ftitle, T_Order.Fnumber,T_Order.FUpdateDate,T_Order.Fqty,T_Order.Fprice,T_Order.Fvolume,T_Order.FtaxPrice,tbdc.FName as CustName,", strOrderBy);
           strCommandText.Append(" tbdcd.FDreeName,tbdct.FName as ContactName,tbds.FName as stockName,case T_Order.Fstatus when 0 then '起草阶段' when 5 then '被驳回' when 2 then '已通过' when 4 then '被拒绝' when 1 then '待审批' else '处理中' end as StatusName,T_Order.Fstatus ");
           strCommandText.Append(" FROM T_Order left join T_BasicData_Customer as tbdc on T_Order.FCustomersID=tbdc.FItemId ");
           strCommandText.Append(" left join T_BasicData_CusDree as tbdcd on T_Order.FCusDreeID=tbdcd.FItemId ");
           strCommandText.Append(" left join T_BasicData_Contact as tbdct on T_Order.FcontactID=tbdct.FItemId ");
           strCommandText.Append(" left join T_BasicData_stock as tbds on T_Order.FstockID=tbds.FitemId ");
           strCommandText.Append(strWhere);

           System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
           DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
           DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
           DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
           DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
           DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
           DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
           DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
           ds = DB.ExecuteDataSet(sqlcommand);
           recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
           pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));

           return ds.Tables[0];
       }

       public void Delete(int orderId)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append(" begin transaction ");
           strSql.Append(" delete T_Order where Fitemid=@Id");
           strSql.Append(" delete T_OrderEntry where FOrderID=@Id");
           strSql.Append(" IF (@@ERROR>0) ROLLBACK ELSE COMMIT ");

           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@Id", DbType.Int32, orderId);
           DB.ExecuteNonQuery(cmd);
       }

       public DataTable GetOrderDetail(int orderId)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append(
               "select Fitemid,FOrderID,FEntryID,T_OrderEntry.FProductId,T_OrderEntry.Fqty,FUnitVolume,Fqtyvolume,Funit,Ftax,");
           strSql.Append(" Fprice,Ftaxprice,Famount,Ftaxamount,Ffanli,Fnote,FProductName,a.storageNo from T_OrderEntry ");
           strSql.Append(
               " left join (SELECT FProductID,SUM(FQTY) AS storageNo FROM T_Stockbal WHERE FDeptID > 0 GROUP BY FProductID) as a on T_OrderEntry.FProductId=a.FProductID ");
           strSql.Append(" where FOrderID=@orderId");
           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@orderId", DbType.Int32, orderId);
           return  DB.ExecuteDataSet(cmd).Tables[0];
       }

       public void Submit(int orderId, int status)
       {
           string strSPName = "sp_SubmitOrder";
           if(status==5)
           {
               strSPName = "sp_ReSubmit"; 
           }
           DbCommand cmd = DB.GetStoredProcCommand(strSPName);
           DB.AddInParameter(cmd, "@FBillInterId", DbType.Int32, orderId);
           DB.AddInParameter(cmd, "@FBillType", DbType.Int32, 1);
           
           DB.ExecuteNonQuery(cmd);
       }

       public DataTable GetOrderProcess(int orderId)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("select * from (SELECT  a.FBillInterID,a.FEmpID, tbc.FName, a.FStep,CASE a.FStatus WHEN 1 THEN '待处理' WHEN 2 THEN '同意' WHEN 3 THEN '驳回' WHEN 4 THEN '不同意' WHEN 5 THEN '驳回到提交人' ELSE '未处理'  END AS result, ");
           strSql.Append(
               " a.FCheckDate, a.FNote,a.FStatus FROM  (SELECT * FROM  S_Approval WHERE FBillInterID =@orderId AND FBillType = 1 AND FISCancel = 1) AS a LEFT OUTER JOIN");
           strSql.Append( "  T_BasicData_Customer AS tbc ON a.FChecker = tbc.FItemId");
           strSql.Append(
               " union SELECT b.FBillInterID,b.FEmpID,tbe.EmpName,b.FStep,CASE b.FStatus WHEN 1 THEN '待处理' WHEN 2 THEN '同意' WHEN 3 THEN '驳回' WHEN 4 THEN '不同意' WHEN 5 THEN '驳回到提交人' ELSE '未处理'  END AS result,");
           strSql.Append(
               " b.FCheckDate, b.FNote,b.FStatus FROM (SELECT * FROM S_Approval WHERE FBillInterID =@orderId AND FBillType = 1 AND FISCancel <> 1) AS b LEFT OUTER JOIN");
           strSql.Append(" T_BasicData_Employee AS tbe ON b.FEmpID = tbe.EmpId ) as C Order by FStep");
           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@orderId", DbType.Int32, orderId);
           return DB.ExecuteDataSet(cmd).Tables[0];
       }

       public DataTable GetOrdersToDo(int empId)
       {
           DbCommand cmd = DB.GetStoredProcCommand("sp_GetOrdersToDo");
           DB.AddInParameter(cmd, "@empId", DbType.Int32, empId);
           return DB.ExecuteDataSet(cmd).Tables[0];
       }

       public void HandleOrder(int empId, int orderId, int status, string notes, int bhStep)
       {
           DbCommand cmd = DB.GetStoredProcCommand("sp_DoProcess");
           DB.AddInParameter(cmd, "@EmpID", DbType.Int32, empId);
           DB.AddInParameter(cmd, "@FBillInterId", DbType.Int32, orderId);
           DB.AddInParameter(cmd, "@FBillType", DbType.Int32, 1);
           DB.AddInParameter(cmd, "@Status", DbType.Int32, status);
           if (status == 3)
           {
               DB.AddInParameter(cmd, "@bhSort", DbType.Int32, bhStep);
           }
           else
           {
               DB.AddInParameter(cmd, "@bhSort", DbType.Int32, null);
           }
           DB.AddInParameter(cmd, "@ChuliYijian", DbType.String, notes);
           DB.ExecuteNonQuery(cmd);
       }
    }
}
